﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace Blog_DAL
{
    /// <summary>
    /// 用户数据库操作类
    /// </summary>
    public class UserDAL : IDbDal
    {
        /// <summary>
        /// 返回单个用户
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.Users</typeparam>
        /// <param name="t">泛型:Blog_MODEL.Users</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public T Query<T>(T t)
        {
            Blog_MODEL.Users user = t as Blog_MODEL.Users;
            String strSql = "select * from Users where 1=1 ";
            if (user.Id != 0)
            {
                strSql += " and Id=@Id ";
            }
            if (!string.IsNullOrEmpty(user.UserName))
            {
                strSql += " and UserName=@UserName ";
            }
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<T>(strSql, user).SingleOrDefault();
            }
        }
        /// <summary>
        /// 查询多个用户
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.Users</typeparam>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public List<T> QueryList<T>(T t)
        {
            Blog_MODEL.Users user = t as Blog_MODEL.Users;
            var Parameters = new DynamicParameters();
            StringBuilder strSql = new StringBuilder(20);
            strSql.AppendLine("select * from Users  WHERE 1=1");
            //拼接条件
            if (!string.IsNullOrEmpty(user.UserName))
            {
                strSql.AppendLine(" and UserName like @UserName ");
                Parameters.Add("UserName", "%" + user.UserName + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserPhone))
            {
                strSql.AppendLine(" and UserPhone like @UserPhone ");
                Parameters.Add("UserPhone", "%" + user.UserPhone + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserNikeName))
            {
                strSql.AppendLine(" and UserNikeName like @UserNikeName ");
                Parameters.Add("UserNikeName", "%" + user.UserNikeName + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserQQ))
            {
                strSql.AppendLine(" and UserQQ like @UserQQ ");
                Parameters.Add("UserQQ", "%" + user.UserQQ + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserWX))
            {
                strSql.AppendLine(" and UserWX like @UserWX ");
                Parameters.Add("UserWX", "%" + user.UserWX + "%", DbType.String);
            }
            if (user.UserStatus != 0)
            {
                strSql.AppendLine(" and UserStatus=@UserStatus ");
                Parameters.Add("UserStatus", user.UserStatus);
            }
            if (!string.IsNullOrEmpty(user.UsersNote))
            {
                strSql.AppendLine(" and UsersNote like @UsersNote ");
                Parameters.Add("UsersNote", "%" + user.UsersNote + "%", DbType.String);
            }

            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<T>(strSql.ToString(), Parameters).ToList();
            }
        }
        /// <summary>
        /// 删除单个用户
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.Users</typeparam>
        /// <param name="t">泛型:Blog_MODEL.Users</param>
        /// <returns></returns>
        public Int32 Delete<T>(T t)
        {
            Blog_MODEL.Users user = t as Blog_MODEL.Users;
            String strSql = "delete from Users where id=@Id ";

            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Execute(strSql, user);
            }

        }
        /// <summary>
        /// 修改用户
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.Users</typeparam>
        /// <param name="t">泛型:Blog_MODEL.Users</param>
        /// <returns></returns>
        public Int32 Update<T>(T t)
        {
            Blog_MODEL.Users user = t as Blog_MODEL.Users;
            var Parameters = new DynamicParameters();
            StringBuilder strSql = new StringBuilder(20);
            strSql.AppendLine(" UPDATE USERS SET ");
            //拼接条件
            if (!string.IsNullOrEmpty(user.UserNikeName))
            {
                strSql.AppendLine(" UserNikeName=@UserNikeName, ");
                Parameters.Add("UserNikeName", user.UserNikeName, DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserPhone))
            {
                strSql.AppendLine(" UserPhone=@UserPhone, ");
                Parameters.Add("UserPhone", user.UserPhone, DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserPwd))
            {
                strSql.AppendLine(" UserPwd=@UserPwd, ");
                Parameters.Add("UserPwd", user.UserPwd, DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserQQ))
            {
                strSql.AppendLine(" UserQQ=@UserQQ, ");
                Parameters.Add("UserQQ", user.UserQQ, DbType.String);
            }
            if (!string.IsNullOrEmpty(user.UserWX))
            {
                strSql.AppendLine(" UserWX=@UserWX, ");
                Parameters.Add("UserWX", user.UserWX, DbType.String);
            }
            if (user.UserStatus != 0)
            {
                strSql.AppendLine(" UserStatus=@UserStatus, ");
                Parameters.Add("UserStatus", user.UserStatus, DbType.Int32);
            }
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Execute(strSql.ToString(), Parameters);
            }
        }
        /// <summary>
        /// 插入用户
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.Users</typeparam>
        /// <param name="t">泛型:Blog_MODEL.Users</param>
        /// <returns></returns>
        public Int32 Insert<T>(T t)
        {
            Blog_MODEL.Users user = t as Blog_MODEL.Users;
            String strSql = @"INSERT INTO [zl_blog].[dbo].[Users]
                                       ([UserName]
                                       ,[UserNikeName]
                                       ,[UserPwd]
                                       ,[UserBirthday]
                                       ,[UserQQ]
                                       ,[UserWX]
                                       ,[UserAvatar]
                                       ,[UserPhone]
                                       ,[UsersNote])
                                 VALUES(@UserName
                                       ,@UserNikeName
                                       ,@UserPwd
                                       ,@UserBirthday
                                       ,@UserQQ
                                       ,@UserWX
                                       ,@UserAvatar
                                       ,@UserPhone
                                       @UsersNote)
                            select @@identity  ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<int>(strSql, user).First();
            }
        }
    }

}
